# 远程SQL Server连接测试脚本
# 使用方法: .\test-remote-connection.ps1

Write-Host "=== 远程SQL Server连接测试 ===" -ForegroundColor Cyan

# 连接参数
$server = "123.56.75.137"
$port = "1433"
$username = "sa"
$password = "q1w2e3r4..."
$database = "MES0825"

Write-Host "`n连接信息:" -ForegroundColor White
Write-Host "服务器: $server" -ForegroundColor Gray
Write-Host "端口: $port" -ForegroundColor Gray
Write-Host "用户名: $username" -ForegroundColor Gray
Write-Host "数据库: $database" -ForegroundColor Gray

# 1. 测试网络连接
Write-Host "`n1. 测试网络连接..." -ForegroundColor White
try {
    $pingResult = Test-NetConnection -ComputerName $server -Port $port -InformationLevel Quiet
    if ($pingResult) {
        Write-Host "✓ 网络连接成功" -ForegroundColor Green
    } else {
        Write-Host "✗ 网络连接失败" -ForegroundColor Red
        Write-Host "可能的原因:" -ForegroundColor Yellow
        Write-Host "- 防火墙阻止连接" -ForegroundColor Gray
        Write-Host "- 服务器未运行" -ForegroundColor Gray
        Write-Host "- 端口被占用" -ForegroundColor Gray
        exit 1
    }
} catch {
    Write-Host "✗ 网络测试失败: $($_.Exception.Message)" -ForegroundColor Red
    exit 1
}

# 2. 测试SQL Server连接
Write-Host "`n2. 测试SQL Server连接..." -ForegroundColor White

# 检查是否安装了SQL Server客户端工具
$sqlcmdPath = $null
try {
    $sqlcmdPath = Get-Command sqlcmd -ErrorAction Stop
    Write-Host "✓ 找到sqlcmd工具: $($sqlcmdPath.Source)" -ForegroundColor Green
} catch {
    Write-Host "⚠ 未找到sqlcmd工具，尝试使用PowerShell连接" -ForegroundColor Yellow
}

if ($sqlcmdPath) {
    # 使用sqlcmd测试连接
    try {
        $testQuery = "SELECT @@VERSION as Version, GETDATE() as CurrentTime"
        $result = & sqlcmd -S "$server,$port" -U $username -P $password -Q $testQuery -h -1
        
        if ($LASTEXITCODE -eq 0) {
            Write-Host "✓ SQL Server连接成功" -ForegroundColor Green
            Write-Host "服务器信息:" -ForegroundColor Gray
            Write-Host $result -ForegroundColor Gray
        } else {
            Write-Host "✗ SQL Server连接失败" -ForegroundColor Red
        }
    } catch {
        Write-Host "✗ sqlcmd执行失败: $($_.Exception.Message)" -ForegroundColor Red
    }
} else {
    # 使用PowerShell测试连接
    Write-Host "使用PowerShell测试连接..." -ForegroundColor Yellow
    
    # 检查是否安装了SqlServer模块
    try {
        Import-Module SqlServer -ErrorAction Stop
        Write-Host "✓ 找到SqlServer PowerShell模块" -ForegroundColor Green
        
        $connectionString = "Server=$server,$port;Database=master;User Id=$username;Password=$password;TrustServerCertificate=true;Encrypt=true"
        
        try {
            $result = Invoke-Sqlcmd -ConnectionString $connectionString -Query "SELECT @@VERSION as Version, GETDATE() as CurrentTime"
            Write-Host "✓ SQL Server连接成功" -ForegroundColor Green
            Write-Host "服务器信息:" -ForegroundColor Gray
            $result | Format-Table -AutoSize
        } catch {
            Write-Host "✗ PowerShell SQL连接失败: $($_.Exception.Message)" -ForegroundColor Red
        }
    } catch {
        Write-Host "✗ 未找到SqlServer PowerShell模块" -ForegroundColor Red
        Write-Host "建议安装SqlServer模块: Install-Module -Name SqlServer -Force" -ForegroundColor Yellow
    }
}

# 3. 测试特定数据库连接
Write-Host "`n3. 测试数据库 '$database' 连接..." -ForegroundColor White

if ($sqlcmdPath) {
    try {
        $dbQuery = "SELECT name, state_desc FROM sys.databases WHERE name = '$database'"
        $dbResult = & sqlcmd -S "$server,$port" -U $username -P $password -Q $dbQuery -h -1
        
        if ($LASTEXITCODE -eq 0) {
            if ($dbResult -match $database) {
                Write-Host "✓ 数据库 '$database' 存在" -ForegroundColor Green
                Write-Host "数据库状态:" -ForegroundColor Gray
                Write-Host $dbResult -ForegroundColor Gray
            } else {
                Write-Host "⚠ 数据库 '$database' 不存在" -ForegroundColor Yellow
                Write-Host "尝试创建数据库..." -ForegroundColor Yellow
                
                $createDbQuery = "CREATE DATABASE [$database]"
                $createResult = & sqlcmd -S "$server,$port" -U $username -P $password -Q $createDbQuery -h -1
                
                if ($LASTEXITCODE -eq 0) {
                    Write-Host "✓ 数据库 '$database' 创建成功" -ForegroundColor Green
                } else {
                    Write-Host "✗ 数据库创建失败" -ForegroundColor Red
                    Write-Host "错误信息:" -ForegroundColor Gray
                    Write-Host $createResult -ForegroundColor Gray
                }
            }
        } else {
            Write-Host "✗ 无法查询数据库信息" -ForegroundColor Red
        }
    } catch {
        Write-Host "✗ 数据库测试失败: $($_.Exception.Message)" -ForegroundColor Red
    }
}

# 4. 提供解决方案建议
Write-Host "`n=== 常见问题解决方案 ===" -ForegroundColor Cyan

Write-Host "`n如果遇到网络连接问题:" -ForegroundColor White
Write-Host "1. 检查防火墙设置" -ForegroundColor Gray
Write-Host "2. 确认服务器IP地址正确" -ForegroundColor Gray
Write-Host "3. 检查端口1433是否开放" -ForegroundColor Gray

Write-Host "`n如果遇到认证问题:" -ForegroundColor White
Write-Host "1. 确认用户名和密码正确" -ForegroundColor Gray
Write-Host "2. 检查SA账户是否启用" -ForegroundColor Gray
Write-Host "3. 确认SQL Server允许远程连接" -ForegroundColor Gray

Write-Host "`n如果遇到加密问题:" -ForegroundColor White
Write-Host "1. 在连接字符串中添加 TrustServerCertificate=true" -ForegroundColor Gray
Write-Host "2. 设置 Encrypt=true 或 Encrypt=false" -ForegroundColor Gray
Write-Host "3. 检查服务器证书配置" -ForegroundColor Gray

Write-Host "`n如果遇到数据库创建问题:" -ForegroundColor White
Write-Host "1. 确认SA账户有创建数据库权限" -ForegroundColor Gray
Write-Host "2. 检查服务器磁盘空间" -ForegroundColor Gray
Write-Host "3. 检查SQL Server服务状态" -ForegroundColor Gray

Write-Host "`n=== 推荐的连接字符串 ===" -ForegroundColor Cyan
Write-Host "标准连接:" -ForegroundColor White
Write-Host "Server=$server,$port;Database=$database;User Id=$username;Password=$password;TrustServerCertificate=true;Encrypt=true" -ForegroundColor Gray

Write-Host "`n简化连接:" -ForegroundColor White
Write-Host "Server=$server;Database=$database;User Id=$username;Password=$password;TrustServerCertificate=true" -ForegroundColor Gray

Write-Host "`n=== 下一步操作 ===" -ForegroundColor Cyan
Write-Host "1. 如果连接成功，检查数据库是否存在" -ForegroundColor White
Write-Host "2. 如果数据库不存在，尝试创建数据库" -ForegroundColor White
Write-Host "3. 如果仍有问题，联系服务器管理员" -ForegroundColor White

Write-Host "`n✓ 连接测试完成!" -ForegroundColor Green
